package com.cn.ccp.service.course;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;

import com.cn.ccp.action.BaseAction;
import com.cn.ccp.bean.CourseBean;
import com.cn.ccp.bean.CourseEvaluationBean;
import com.cn.ccp.bean.CourseNoticeBean;
import com.cn.ccp.bean.CourseOutlineBean;
import com.cn.ccp.bean.CourseReleaseRecordBean;
import com.cn.ccp.bean.CourseSettingBean;
import com.cn.ccp.bean.CoursesApproveBean;
import com.cn.ccp.bean.ResourseBean;
import com.cn.ccp.service.BasicService;
import com.cn.ccp.util.CCPContants;
import com.cn.ccp.util.Contants;

public class CourseService extends BasicService{
	private Log log = LogFactory.getLog(CourseService.class);
	
	
	public int getSaveFileId(){
		String sql2 = "SELECT id FROM file_path WHERE flag = true";
		int filePathId = -1;
		try {
			filePathId = jdbcTemplate.queryForInt(sql2);
		} catch (Exception e) {
		}
		return filePathId ;
	}
	
	public int sava(final CourseBean bean){
		final int index = CCPContants.getClobalIndex();
		final  int filePathid = getSaveFileId() ;
		transactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus arg0) {
				try {
					
					
					
					KeyHolder keyHolder = new GeneratedKeyHolder();  
					final String sql = "insert into courses(specialty_id, courses_name, descript,course_type,"+ 
									" image,`status`,has_test,total_score, "+
									" prices,start_level,is_high_quality, "+
									" create_time,online_begin_time,online_end_time,"+ 
									" credit ,teacher_id ,teaching_group_id ,file_path_id,language_type,completed,originate ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
					jdbcTemplate.update(new PreparedStatementCreator() {  
							    public PreparedStatement createPreparedStatement(Connection con) throws SQLException {  
								     PreparedStatement ps =con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
								     
								     ps.setObject(1 ,bean.getSpecialty_id());
						    		 ps.setObject(2 ,bean.getCourse_name());
								     ps.setObject(3 ,bean.getDescripte());
								     ps.setObject(4 ,bean.getKeys());
								     ps.setObject(5 ,bean.getImage());
								     ps.setObject(6 ,bean.getStatus());
								     ps.setObject(7 ,bean.getHas_test());
								     ps.setObject(8 ,bean.getTotal_score());
								     ps.setObject(9 ,bean.getPrices());
								     ps.setObject(10 ,bean.getStart_level());
								     ps.setObject(11 ,bean.getIs_high_quality());
								     ps.setObject(12 ,bean.getCreate_time() );
								     ps.setObject(13 ,bean.getOnline_begin_time());
								     ps.setObject(14 ,bean.getOnline_end_time());
								     ps.setObject(15, bean.getCredit());
								     ps.setObject(16, CCPContants.getCurrUserId());
								     ps.setObject(17, bean.getTeaching_group_id());
								     ps.setObject(18 , filePathid);
								     ps.setObject(19 , bean.getLanguage_type());
								     ps.setObject(20 , bean.getCompleted());
								     ps.setObject(21 , bean.getCourseResourse());
								     return ps;  
							    }  
							   }, keyHolder);  
					int courseid = keyHolder.getKey().intValue();
					CCPContants.tranmitResultMap.put(index, courseid);
				} catch (Exception e) {
					arg0.setRollbackOnly();
					e.printStackTrace();
					CCPContants.tranmitResultMap.put(index, 0);
				}
				return null;
			}
		});
		return (Integer) CCPContants.tranmitResultMap.get(index) ;
	
	}
	
	public int getTotalScore(String courseid ,String type){
		String sql = "SELECT SUM(training_score) FROM training WHERE chapter_course_id = ? and is_chapter = ?";
		try {
			return jdbcTemplate.queryForInt(sql,new Object[]{courseid, type}) ;
		} catch (Exception e) {
			log.error("query score failed : ",e);
		}
		return 0 ;
	}
	
	public boolean update(final CourseBean bean ){
		final int index = CCPContants.getClobalIndex();
		transactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus arg0) {
				try {
					
					String oldImage = getBeanById(bean.getId()).getImage();
					
					if(!oldImage.equalsIgnoreCase(bean.getImage())){
						File file = new File(CCPContants.SYS_PATH+File.separator+oldImage);
						if(file.exists()) file.delete(); 
					}
					String sql2 = "SELECT id FROM file_path WHERE flag = true";
					int filePathId = -1;
					try {
						filePathId = jdbcTemplate.queryForInt(sql2);
					} catch (Exception e) {
					}
					final String sql = "update courses set specialty_id=?, courses_name=?, descript=?,course_type=?,"+ 
										" image=?,status=?,has_test=?,total_score=?, "+
										" prices=?,start_level=?,is_high_quality=?, "+
										" create_time=?,"+ 
										" credit=? ,teaching_group_id = ? , file_path_id = ? ,language_type=?,completed=?,originate=? where id = ?";
					
					Object[] objs = new Object[]{bean.getSpecialty_id() ,bean.getCourse_name(),bean.getDescripte() ,bean.getKeys()
							,bean.getImage() ,bean.getStatus() ,bean.getHas_test() ,bean.getTotal_score(),
							bean.getPrices() ,bean.getStart_level() ,bean.getIs_high_quality(),
							bean.getCreate_time() ,
							bean.getCredit() ,bean.getTeaching_group_id() ,filePathId,bean.getLanguage_type() ,bean.getCompleted() ,bean.getCourseResourse() ,bean.getId()};
					log.info(bean);
					jdbcTemplate.update(sql ,objs);  
					if(bean.getStatus().equals("3"))
						updateCourseStatus(bean.getId(), "1");
					CCPContants.tranmitResultMap.put(index, true);
				} catch (Exception e) {
					e.printStackTrace();
					CCPContants.tranmitResultMap.put(index, false);
				}
				return null;
			}
		});
		return (Boolean) CCPContants.tranmitResultMap.get(index) ;
	}
	public void updateCourseStatus(String id , String status){
		String sql = "update courses set `status` = ? where id = ?";
		jdbcTemplate.update(sql, new Object[]{status ,id});
	}
	
	public CourseBean getBeanById(String id ){
		String sql = "SELECT c.* ,s.specialtyname ,u.username ,tg.teaching_group_name,u.descript as userdescript,count(distinct ss.id) as studys   "+
					" FROM courses c "+
					" left outer join eduonline.selected_course ss  on c.id = ss.course_id and ss.status = 1"+
					" LEFT OUTER JOIN teaching_group tg ON tg.id = c.teaching_group_id "+
					" LEFT OUTER  JOIN specialty s ON s.id = c.specialty_id "+
					" LEFT OUTER JOIN USER u ON u.id = c.teacher_id "+
					" WHERE c.id = ?";
		try {
			return (CourseBean) jdbcTemplate.queryForObject(sql, new Object[]{id} , new CourseRowMapper());
		} catch (Exception e) {
			log.error("query failed : ", e);
		}
		return new CourseBean();
	}
	
	public CourseBean getBeanByCapterId(String id ){
		String sql = "SELECT c.* FROM courses c LEFT OUTER JOIN courses_detail cd ON cd.courses_id = c.id WHERE cd.id = ?";
		try {
			return (CourseBean) jdbcTemplate.queryForObject(sql, new Object[]{id} , new CourseRowMapper());
		} catch (Exception e) {
		}
		return new CourseBean();
	}
	
	public boolean delete(final String id) {
		final int index = CCPContants.getClobalIndex();
		transactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus arg0) {
				try {
					CourseBean cb = getBeanById(id);
					//删除课程试题
					String sql = "delete from training where chapter_course_id = ? and is_chapter = '1'";
					jdbcTemplate.update(sql , new Object[]{id});
					//删除课程章节试题
					sql = "DELETE FROM training WHERE chapter_course_id NOT IN (SELECT id FROM courses_detail WHERE courses_id  = ?)";
					jdbcTemplate.update(sql , new Object[]{id});
					//删除章节信息
					sql = "delete from courses_detail where courses_id = ?";
					jdbcTemplate.update(sql , new Object[]{id});
					//删除课程信息
					sql = "delete from courses where id = ?";
					jdbcTemplate.update(sql , new Object[]{id});
					CCPContants.tranmitResultMap.put(index, true);
					
					File file = new File(CCPContants.SYS_PATH+File.separator+"files"+File.separator+cb.getImage());
					if(file.exists()) file.delete(); 
				} catch (Exception e) {
					arg0.setRollbackOnly();
					CCPContants.tranmitResultMap.put(index, false);
				}
				return null;
			}
		});
		return (Boolean) CCPContants.tranmitResultMap.get(index);
	}
	
	public int getCount(){
		String sql = "SELECT count(1) FROM courses where teacher_id = ?";
		return jdbcTemplate.queryForInt(sql ,new Object[]{CCPContants.getCurrUserId()});
	}
	
	
	public List<CourseBean> getAllCourse(){
		String sql = "SELECT c.* ,s.specialtyname ,u.username ,cd.id as cdid "+
					" FROM courses c "+
					" LEFT OUTER  JOIN specialty s ON s.id = c.specialty_id "+
					" LEFT OUTER JOIN USER u ON u.id = c.teacher_id "+
					" LEFT OUTER JOIN courses_detail cd ON cd.courses_id = c.id "+
					" GROUP BY c.id ORDER BY id DESC ";
		return jdbcTemplate.query(sql, new CourseRowMapper());
	}
	
	public List<CourseBean> pageQuery(int start,int pagesize){
		String sql = "SELECT c.* ,s.specialtyname ,u.username ,cd.id as cdid "+
					" FROM courses c "+
					" LEFT OUTER  JOIN specialty s ON s.id = c.specialty_id "+
					" LEFT OUTER JOIN USER u ON u.id = c.teacher_id "+
					" LEFT OUTER JOIN courses_detail cd ON cd.courses_id = c.id "+
					" where c.teacher_id = ? GROUP BY c.id ORDER BY STATUS  ASC ,create_time DESC    LIMIT ?,?";
		return jdbcTemplate.query(sql, new Object[]{CCPContants.getCurrUserId() ,start,pagesize}, new CourseRowMapper());
	}
	
	/**
	 * 待审核课程总数
	 * @return
	 */
	public int getCountReleaseApproval(){
		String sql = "SELECT count(1) "+
					" FROM courses c "+ 
					" LEFT OUTER JOIN USER u ON u.id = c.teacher_id "+ 
					" LEFT OUTER JOIN user_teaching_group utg ON utg.teaching_group_id = c.teaching_group_id "+
					" WHERE c.status=2 AND utg.user_id = ?";
		return jdbcTemplate.queryForInt(sql ,new Object[]{CCPContants.getUserBean().getId()});
	}
	
	public Object getCourseApprovalOpinion(String courseid){
		String sql = "SELECT approval_opinion FROM courses_need_approve WHERE courses_id = ? ORDER BY id DESC LIMIT 0,1";
		Object obj = null;
		try{
			obj = jdbcTemplate.queryForObject(sql, new Object[]{courseid}, String.class);
		}catch(Exception e){}
		return obj;
	}
	
	/**
	 * 查询需要考试/练习的课程
	 * @param start
	 * @param pagesize
	 * @return
	 */
	public List<CourseBean> getTestCourseList(int start,int pagesize ){
		String sql = "SELECT c.*, s.specialtyname,u.username,  SUM(t.training_score) AS trainingTotalScore   ,cd.id AS cdid "+
					" FROM courses c "+ 
					" LEFT OUTER JOIN specialty s ON s.id = c.specialty_id "+ 
					" LEFT OUTER JOIN USER u ON u.id = c.teacher_id "+ 
					" LEFT OUTER JOIN training t ON t.chapter_course_id = c.id "+ 
					" LEFT OUTER JOIN (SELECT * FROM courses_detail WHERE has_training = 1 GROUP BY courses_id) cd ON cd.courses_id = c.id "+ 
					" WHERE c.has_test = 1 "+ 
					" AND c.teacher_id = ? "+ 
					" AND (c.`status` = 1 OR c.`status` = 3 ) "+
					" GROUP BY c.id  "
				+ " LIMIT ?,?";
		return jdbcTemplate.query(sql, new Object[]{CCPContants.getCurrUserId() ,start,pagesize}, new CourseRowMapper());
	}
	
	public int getTestCount(){
		String sql = "select count(1) from courses where has_test = 1 and teacher_id = ?";
		return jdbcTemplate.queryForInt(sql ,new Object[]{CCPContants.getCurrUserId()});
	}
	
	
	/**
	 * 查询待发布审核课程
	 * @param start
	 * @param pagesize
	 * @return
	 */
	public List<CourseBean> pageQueryForReleaseApproval(int start,int pagesize){
		String sql = "SELECT c.*  ,u.username,tg.teaching_group_name "+
					" FROM courses c "+ 
					" LEFT OUTER JOIN USER u ON u.id = c.teacher_id "+ 
					" LEFT OUTER JOIN teaching_group tg ON tg.id = c.teaching_group_id "+
					" LEFT OUTER JOIN user_teaching_group utg ON utg.teaching_group_id = c.teaching_group_id "+
					" WHERE c.status=2 AND utg.user_id = ? LIMIT ?,?";
		return jdbcTemplate.query(sql, new Object[]{CCPContants.getUserBean().getId(),start,pagesize}, new CourseRowMapper());
	}
	
	
	/**
	 * 发布课程总数
	 * @return
	 */
	public int getCountReleaseForUser(){
		String sql = "SELECT count(1) "+
					" FROM courses c "+ 
					" WHERE c.status = 4  ";
		return jdbcTemplate.queryForInt(sql );
	}
	
	
	/**
	 * 下架课程总数
	 * @return
	 */
	public int getCountDownForUser(){
		String sql = "SELECT count(1) "+
					" FROM courses c "+ 
					" LEFT OUTER JOIN USER u ON u.id = c.teacher_id "+ 
					" LEFT OUTER JOIN teaching_group tg ON tg.id = c.teaching_group_id "+
					" LEFT OUTER JOIN user_teaching_group utg ON utg.teaching_group_id = c.teaching_group_id "+
					" WHERE c.status = 6 AND utg.user_id = ? ";
		return jdbcTemplate.queryForInt(sql ,new Object[]{CCPContants.getUserBean().getId()});
	}
	
	
	/**
	 * 发布课程总数
	 * @param selectedReleaseStatus：课程状态
	 * @param releaseTeacherID：发布老师id
	 * @param courseType：课程类别
	 * @return
	 */
	public int getCountRelease(String selectedReleaseStatus,String releaseTeacherID,String courseType){
		StringBuffer buf = new StringBuffer();
		
		buf.append("SELECT count(1) ");
		buf.append(" FROM courses c ");
		buf.append(" LEFT OUTER JOIN USER u ON u.id = c.teacher_id ");
		List<String> paraList = new ArrayList<String>(); 
		if(selectedReleaseStatus != null && !"".equalsIgnoreCase(selectedReleaseStatus.trim())){
			paraList.add(selectedReleaseStatus);
			buf.append(" WHERE c.status = ?  ");
		}else
			buf.append(" WHERE c.status in (4,5) ");
		
		if(releaseTeacherID != null && !"".equalsIgnoreCase(releaseTeacherID.trim())){
			paraList.add(releaseTeacherID);
			buf.append(" and c.teacher_id = ?  ");
		}
		if(courseType != null && !"".equalsIgnoreCase(courseType.trim())){
			paraList.add(courseType);
			buf.append(" and c.course_type = ?  ");
		}
//		buf.append(" and utg.user_id = ?  ");
//		paraList.add(""+CCPContants.getUserBean().getId());
		return jdbcTemplate.queryForInt(buf.toString() ,paraList.toArray());
	}
	
	/**
	 * 发布课程总数
	 * @return
	 */
	public int getCountQuality(){
		String sql = "SELECT count(1) "+
					" FROM courses c "+ 
					" LEFT OUTER JOIN USER u ON u.id = c.teacher_id "+ 
					" LEFT OUTER JOIN teaching_group tg ON tg.id = c.teaching_group_id "+
					" LEFT OUTER JOIN user_teaching_group utg ON utg.teaching_group_id = c.teaching_group_id "+
					" WHERE c.status = 5 AND utg.user_id = ? ";
		return jdbcTemplate.queryForInt(sql ,new Object[]{CCPContants.getUserBean().getId()});
	}
	
	
	
	/**
	 * 发布审核课程
	 * @param start
	 * @param pagesize
	 * @return
	 */
	public List<CourseBean> pageQueryForRelease(int start,int pagesize,String selectedReleaseStatus,String selectedTeachId,String selectedReleaseType){
		StringBuffer sql = new StringBuffer();
		
		sql.append("SELECT c.*  ,u.username  ");
		sql.append(" FROM courses c ");
		sql.append(" LEFT OUTER JOIN USER u ON u.id = c.teacher_id ");
		List<Object> paraList = new ArrayList<Object>(); 
		if(selectedReleaseStatus != null && !"".equalsIgnoreCase(selectedReleaseStatus.trim())){
			paraList.add(selectedReleaseStatus);
			sql.append(" WHERE c.status = ?  ");
		}else
			sql.append(" WHERE c.status in (4,5) ");
		
		if(selectedTeachId != null && !"".equalsIgnoreCase(selectedTeachId.trim())){
			paraList.add(selectedTeachId);
			sql.append(" and c.teacher_id = ?  ");
		}
		if(selectedReleaseType != null && !"".equalsIgnoreCase(selectedReleaseType.trim())){
			paraList.add(selectedReleaseType);
			sql.append(" and c.course_type = ?  ");
		}
		sql.append("    order by c.status asc LIMIT ?,? ");
		paraList.add(start);
		paraList.add(pagesize);
		return jdbcTemplate.query(sql.toString(), paraList.toArray(), new CourseRowMapper());
	}

	
	/**
	 * 课程评选
	 * @param start
	 * @param pagesize
	 * @return
	 */
	public List<CourseBean> pageQueryForQuality(int start,int pagesize){
		String sql = "SELECT c.*  ,u.username,tg.teaching_group_name "+
					" FROM courses c "+ 
					" LEFT OUTER JOIN USER u ON u.id = c.teacher_id "+ 
					" LEFT OUTER JOIN teaching_group tg ON tg.id = c.teaching_group_id "+
					" LEFT OUTER JOIN user_teaching_group utg ON utg.teaching_group_id = c.teaching_group_id "+
					" WHERE c.status = 5 AND utg.user_id = ? order by c.id desc LIMIT ?,?";
		return jdbcTemplate.query(sql, new Object[]{CCPContants.getUserBean().getId(),start,pagesize}, new CourseRowMapper());
	}
	
	
	/**
	 * 课程审批记录
	 * @param agree：审批同意标志
	 * @param bean：审批记录bean
	 * @return
	 */
	public void doApprove(final boolean agree,final CourseReleaseRecordBean bean){
		transactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus arg0) {
				try {
					String sql = "update courses set status=?,online_begin_time = ? where id = ?";
					if(agree){//审批同意
						jdbcTemplate.update(sql , new Object[]{ Contants.COURSE_STATUS_RELEASE,CCPContants.sdf3.format(new Date()),bean.getCoursesId() });					 
					}else
						jdbcTemplate.update(sql , new Object[]{ Contants.COURSE_STATUS_APPROVE_NO,CCPContants.sdf3.format(new Date()),bean.getCoursesId() });	
					sql = "insert into courses_need_approve(courses_id ,approval_opinion,approve_status,approve_user_id,approve_time) values (?,?,?,?,?)";
					jdbcTemplate.update(sql , new Object[]{bean.getCoursesId() ,bean.getApprovalOpinion(),bean.isApproveStatus(),bean.getApproveUserId(),bean.getApproveTime()});					 
				} catch (Exception e) {
					arg0.setRollbackOnly();
					log.error("courese release approve failed : ", e);
				}
				return null;
			}
		});
	}
	
	/**
	 * 学生对老师课程进行评价
	 * @param selectedCourseId：学生选课id
	 * @param starLevel：评价星级
	 * @param contents：评价详细内容
	 * @param courseId：课程id
	 */
	public void doMarkCourse(final String selectedCourseId,final int starLevel,final String contents,final String courseId){
		transactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus arg0) {
				try {
					String sql = "insert into student_marked_history(student_id ,course_id,start_level,marked_time,student_marked_content) values (?,?,?,?,?)";
					jdbcTemplate.update(sql , new Object[]{CCPContants.getUserBean().getId() ,courseId,starLevel,CCPContants.sdf3.format(new Date()),contents});					 
					sql = "update courses set start_level = "
							+" (select avg(start_level) from student_marked_history where course_id = ? ) where id = ? ";
					jdbcTemplate.update(sql , new Object[]{ courseId,courseId});			
					
					sql = "UPDATE selected_course SET has_marked = 1 WHERE id= ?";
					jdbcTemplate.update(sql ,new Object[]{selectedCourseId});
				} catch (Exception e) {
					arg0.setRollbackOnly();
					log.error("marking course failed : ", e);
				}
				return null;
			}
		});
	}
	
	
	/**
	 * 学生对老师课程进行评价
	 * @param selectedCourseId：学生选课id
	 * @param starLevel：评价星级
	 * @param contents：评价详细内容
	 * @param courseId：课程id
	 */
	public void doMarkCourse(final String selectedCourseId,final int starLevel,final String contents,final String courseId,final String userId){
		transactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus arg0) {
				try {
					String sql = "insert into student_marked_history(student_id ,course_id,start_level,marked_time,student_marked_content) values (?,?,?,?,?)";
					jdbcTemplate.update(sql , new Object[]{userId ,courseId,starLevel,CCPContants.sdf3.format(new Date()),contents});					 
					sql = "update courses set start_level = "
							+" (select avg(start_level) from student_marked_history where course_id = ? ) where id = ? ";
					jdbcTemplate.update(sql , new Object[]{ courseId,courseId});			
					
					sql = "UPDATE selected_course SET has_marked = 1 WHERE id= ?";
					jdbcTemplate.update(sql ,new Object[]{selectedCourseId});
				} catch (Exception e) {
					arg0.setRollbackOnly();
					log.error("marking course failed : ", e);
				}
				return null;
			}
		});
	}
	
	
	public void doUndercarriage(String courseId){
		jdbcTemplate.update("update courses set status = 6 where id = ?" , new Object[]{courseId});		
	}
	
	
	
	public void doRelease(final String courseId,final String prices,final  String beginTime,final String endTime ,final  String selectNodes){
		transactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus arg0) {
				try {
					
					
					//1:0:treeDemo_1:null,1:1:treeDemo_4:treeDemo_1,2:2:treeDemo_7:treeDemo_4,2:1:treeDemo_5:treeDemo_1
					String[] strs = selectNodes.trim().split(",");
					List<CourseMapping> saveList = new ArrayList<CourseMapping>();
					for(String s : strs){
						if(s.equals("") || s.equals("0"))
							continue;
						CourseMapping c = new CourseMapping(s);
						for(Iterator it = saveList.iterator(); it.hasNext();){
							CourseMapping str = (CourseMapping) it.next(); 
							if(c.getParentTId().equalsIgnoreCase(str.getTid()))
								it.remove(); 
						}
						saveList.add(c);
					}
					String sql = "DELETE FROM course_mapping WHERE course_id = ?";
//					if (saveList.size() > 0)
						jdbcTemplate.update(sql,new Object[]{courseId});
					
					sql = "INSERT INTO course_mapping ( course_id ,course_mapping_type ,other_id) VALUES (?,?,?)";
					for(CourseMapping cm:saveList){
						String type = cm.getLevel();
						String id = cm.getId();
						if(type.equals("0")){
							type = "4";
						}else if(type.equals("1")){
							type = "3";
						}else if(type.equals("2")){
							type = "2";
						}else if(type.equals("3")){
							type = "1";
						}
						jdbcTemplate.update(sql ,new Object[]{courseId ,type , id});
					}
					
					
					sql = "update courses set prices=?,online_begin_time = ?, online_end_time=?,create_time=? , status = 5 where id = ?";
					jdbcTemplate.update(sql , new Object[]{ prices, beginTime , endTime,CCPContants.sdf3.format(new Date()),courseId});		
				} catch (Exception e) {
					e.printStackTrace();
					
				}
				return null;
			}
		});
	}
	
	
	public void doQuality(boolean isQuality,String courseId){
		String sql = "update courses set is_high_quality=?  where id = ?";
		jdbcTemplate.update(sql , new Object[]{ isQuality,courseId});					 
	}
	
	
	public List<CoursesApproveBean> getCourseApproveList(String courseId){
		String sql = "SELECT cna.* ,u.username "+
						" FROM courses_need_approve cna "+
						" LEFT OUTER JOIN USER u ON u.id = cna.approve_user_id "+
						" WHERE cna.courses_id = ? "+
						" ORDER BY cna.id DESC ";
		return jdbcTemplate.query(sql, new Object[]{courseId} , new CoursesApproveRowMapper());
	}
	
	/**
	 * 根据课程id查询课程公告
	 * @param courseId
	 * @return
	 */
	public List<CourseNoticeBean> getNoticeByCourseId(String courseId){
		return jdbcTemplate.query("select * from course_notice where course_id = ? ORDER BY create_time DESC ", new Object[]{courseId} , new CoursesNoticeRowMapper());
	}
	
	public CourseNoticeBean getNoticeById(String id){
		return (CourseNoticeBean)jdbcTemplate.queryForObject("select * from course_notice where id = ?  ", new Object[]{id} , new CoursesNoticeRowMapper());
	}
	
	
	/**
	 * 查询学生所选课程的公告信息
	 * @return
	 */
	public List<CourseNoticeBean> getStudentNotice(){
		StringBuffer sql = new StringBuffer();
		sql.append("select cn.id,sc.course_id,u.username,c.courses_name,cn.notice,cn.create_time,cn.teach_id from selected_course sc ");
		sql.append("left join course_notice cn on sc.course_id = cn.course_id  ");
		sql.append("left join user u on u.id = cn.teach_id ");
		sql.append("left join courses c on c.id = sc.course_id ");
		sql.append("where sc.student_id=? and ( DATE_SUB(CURDATE(), INTERVAL 6 MONTH)   <=   cn.create_time) order by cn.create_time desc ");
		return jdbcTemplate.query(sql.toString(), new Object[]{CCPContants.getCurrUserId()} , new StudentCoursesNoticeRowMapper());
		
	}
	
	
	/**
	 * 创建公告
	 * @param courseId
	 * @param content
	 * @return
	 */
	public boolean saveCourseNotice(String courseId , String content){
		String sql = "INSERT INTO course_notice "+
					" (course_id,notice,create_time,teach_id)VALUES(?,?,CURRENT_TIMESTAMP,?);";
		Object[] objs = new Object[]{courseId,content  ,CCPContants.getCurrUserId() };
		return jdbcTemplate.update(sql,objs) > 0; 
	}
	
	public boolean updateCourseNotice(String noteId , String content){
		String sql = "update course_notice set notice = ? ,create_time = CURRENT_TIMESTAMP where id = ?";
		Object[] objs = new Object[]{content  ,noteId };
		return jdbcTemplate.update(sql,objs) > 0; 
	}
	
	public boolean delNote(String noteId){
		String sql = "DELETE FROM course_notice WHERE    id = ?";
		return jdbcTemplate.update(sql,new Object[]{noteId }) > 0; 
	}
	
	/**
	 * 创建课程大纲
	 * @param courseId
	 * @param outine
	 * @return
	 */
	public void saveCourseOutline(final String courseId , final int filePathId,final String fileName){
		transactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus arg0) {
				Object point = arg0.createSavepoint();
				try {
					String sql = "delete from course_outline where course_id = ? ";
					jdbcTemplate.update(sql,new Object[]{courseId}); 
					
					sql = "INSERT INTO course_outline "+
							" (course_id,file_path_id,outline)VALUES(?,?,?);";
				Object[] objs = new Object[]{courseId,filePathId,fileName};
				return jdbcTemplate.update(sql,objs) > 0; 
					
				} catch (Exception e) {
					log.error("create outline failed : ", e);
					arg0.releaseSavepoint(point);
				}
				return null;
			}
		});
		
	}
	
	public boolean updateCourseOutline(int id ,int filePathId, String outline){
		String sql = "update course_outline set outline = ? ,file_path_id = ? where id = ?";
		Object[] objs = new Object[]{outline ,filePathId ,id };
		return jdbcTemplate.update(sql,objs) > 0; 
	}
	
	public CourseOutlineBean  getOutlineByCourseId(String courseId){
		String sql = "select * from course_outline where course_id = ?";
		try{
			return (CourseOutlineBean) jdbcTemplate.queryForObject(sql, new Object[]{courseId }, new CourseOutlineRowMapper());
		}catch(EmptyResultDataAccessException e){
			return null;
		}
	}
	 
	
	public void saveCourseEvaluation(final String courseId ,final int filePathId,final String fileName){
		transactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus arg0) {
				Object point = arg0.createSavepoint();
				try {
					String sql = "delete from course_evaluation where course_id = ? ";
					jdbcTemplate.update(sql,new Object[]{courseId}); 
					
					sql = "INSERT INTO course_evaluation ( course_id, file_path_id, contents)VALUES(?,?,?);";
					Object[] objs = new Object[]{courseId,filePathId ,fileName };
					jdbcTemplate.update(sql,objs) ;
				} catch (Exception e) {
					log.error("create evaluation failed : ", e);
					arg0.releaseSavepoint(point);
				}
				return null;
			}
		});
	}
	
	public boolean updateCourseEvaluation(int id , String evaluationFileName,int filePathId){
		String sql = "update course_evaluation set contents = ? ,file_path_id = ?  where id = ?";
		Object[] objs = new Object[]{evaluationFileName  ,filePathId,id };
		return jdbcTemplate.update(sql,objs) > 0; 
	}
	
	
	public CourseEvaluationBean  getEvaluationByCourseId(String courseId){
		String sql = "select * from course_evaluation where course_id = ?";
		try{
			return (CourseEvaluationBean) jdbcTemplate.queryForObject(sql, new Object[]{courseId }, new CourseEvaluationRowMapper());
		}catch(EmptyResultDataAccessException e){
			return null;
		}
	}
	
	
	public int getCourseSettingCount(String courseId ,String type){
		StringBuffer sql = new StringBuffer();
		Object[] objs = null;
		if(type.equals("0") || type.equals("-1")){
			sql.append("select count(1) from course_setting where course_id = ?");
			objs = new Object[]{courseId};
		}else{
			sql.append("select count(1) from course_setting where course_id = ? and type = ? ");
			objs = new Object[]{courseId , type};
		}
		return jdbcTemplate.queryForInt(sql.toString() , objs);
	}
	
	public List<CourseSettingBean> getCourseSettingList(String courseId ,String type ){
		StringBuffer sql = new StringBuffer();
		Object[] objs = null;
		if(type.equals("0")  || type.equals("-1")){
			sql.append("select * from course_setting where course_id = ? order by type limit ?,?");
			objs = new Object[]{courseId };
		}else{
			sql.append("select * from course_setting where course_id = ? and type = ?  order by type ");
			objs = new Object[]{courseId ,type};
		}
		return jdbcTemplate.query(sql.toString() ,objs, new CourseSettingRowMapper());
	}
	
	public List<CourseSettingBean> getCourseSetting(String courseId ,String type , int start, int pagesize){
		StringBuffer sql = new StringBuffer();
		Object[] objs = null;
		if(type.equals("0")  || type.equals("-1")){
			sql.append("select * from course_setting where course_id = ? order by type limit ?,?");
			objs = new Object[]{courseId , start ,pagesize};
		}else{
			sql.append("select * from course_setting where course_id = ? and type = ?  order by type limit ?,?");
			objs = new Object[]{courseId ,type, start ,pagesize};
		}
		return jdbcTemplate.query(sql.toString() ,objs, new CourseSettingRowMapper());
	}
	
	public void updateActiv(String courseId ,String selectType ,String id , String active){
		if(selectType.equals("3")){
			CourseSettingBean bean = getCourseSetingBean(id);
			String sql = "";
			if(bean.getActive().equals("0")){
				sql = "UPDATE course_setting set active = 1  WHERE id = ?";
			}else{
				sql = "UPDATE course_setting set active = 0  WHERE id = ?";
			}
			Object[] objs = new Object[]{id };
			jdbcTemplate.update(sql ,objs);
		}else{
			String sql = "UPDATE course_setting set active = 0  WHERE active = 1 and course_id = ?  AND TYPE = ?";
			Object[] objs = new Object[]{courseId   ,selectType};
			jdbcTemplate.update(sql ,objs);
			
			sql = "UPDATE course_setting set active = 1  WHERE id = ?";
			objs = new Object[]{id };
			jdbcTemplate.update(sql ,objs);
		}
	}
	
	public void updateActiv(String courseId ,String selectType  ){
		String sql = "UPDATE course_setting set active = 0  WHERE active = 1 and course_id = ?  AND TYPE = ?";
		Object[] objs = new Object[]{courseId ,selectType};
		jdbcTemplate.update(sql ,objs);
	}
	
	
	public List<CourseSettingBean> getCourseSetting(String type){
		StringBuffer sql = new StringBuffer();
		if(type.equals("0")  || type.equals("-1"))
			sql.append("select * from course_setting  order by type");
		else
			sql.append("select * from course_setting where type = ?  order by type");
		return jdbcTemplate.query(sql.toString() ,new Object[]{type}, new CourseSettingRowMapper());
	}
	
	
	public int onlineTeachCount(String userId){
		//查询该该老师的授课总数
		StringBuffer sql = new StringBuffer();
		sql.append(" select sum(cnt) from ( ");
		sql.append(" select count(id) as cnt from courses where teacher_id =  ? and status=5 ");
		sql.append(" union all  ");
		sql.append(" select count(id) as cnt from course_teach_mapping where teach_id = ?  ");
		sql.append("  )as countdata ");
		
		
		
//		StringBuffer sql = new StringBuffer();
//		sql.append(" select count(1)  ");
//		sql.append(" from courses c ");
//		sql.append(" left outer join user u on u.id =  c.teacher_id");
//		sql.append(" where teacher_id = ? and c.status=5 ");
//		sql.append(" union all select c.file_path_id, c.id,c.courses_name,c.start_level,c.image,c.teacher_id ,c.create_time,c.originate,u.username");  
//		sql.append(" from courses c ");
//		sql.append(" left outer join user u on u.id =  c.teacher_id");
//		sql.append(" where c.id in (select course_id from course_teach_mapping where teach_id = ?) and c.status=5");
//		sql.append(" order by id limit ?,?");
		
		
		return jdbcTemplate.queryForInt(sql.toString(), new Object[]{userId,userId});
	}
	
	
	public List<CourseBean> onlineTeach(String userId,int start,int pagesize){
		StringBuffer sql = new StringBuffer();
		sql.append(" select c.file_path_id, c.id,c.courses_name,c.start_level,c.image,c.teacher_id ,c.create_time,c.originate,u.username  ");
		sql.append(" from courses c ");
		sql.append(" left outer join user u on u.id =  c.teacher_id");
		sql.append(" where teacher_id = ? and c.status=5 ");
		sql.append(" union all select c.file_path_id, c.id,c.courses_name,c.start_level,c.image,c.teacher_id ,c.create_time,c.originate,u.username");  
		sql.append(" from courses c ");
		sql.append(" left outer join user u on u.id =  c.teacher_id");
		sql.append(" where c.id in (select course_id from course_teach_mapping where teach_id = ?) and c.status=5");
		sql.append(" order by id limit ?,?");
		
		return jdbcTemplate.query(sql.toString(),new Object[]{userId,userId,start,pagesize}, new CourseMapper());
	}
	
	/**
	 * 根据老师id查询该老师所属课程的状态信息
	 * @param teacherId
	 * @return
	 */
	public List<String> getCourseStatusByUserId(int teacherId){
		String sql = "select status from courses where teacher_id = ? ";
		return jdbcTemplate.queryForList(sql,new Object[]{teacherId}, String.class);
	}
	
	
	/**
	 * 查询其他老师课程总数
	 * @return
	 */
	public int otherCourseCount(String userId){
		return jdbcTemplate.queryForInt("select count(id) from courses where teacher_id <> ? ", new Object[]{userId});
	}
	
	
	public List<CourseBean> otherCourse(String userId,int start,int pagesize){
		StringBuffer sql = new StringBuffer();
		sql.append(" select c.file_path_id, c.id,c.courses_name,c.start_level,c.image,c.teacher_id ,c.create_time,c.originate,u.username ,ctm.id as ctmid  ");
		sql.append(" from courses c ");
		sql.append(" left outer join user u on u.id =  c.teacher_id");
		sql.append(" left outer join course_teach_mapping ctm on ctm.course_id = c.id and ctm.teach_id = ?");
		sql.append(" where c.teacher_id <> ? and c.status=5 ");
		sql.append(" order by c.id limit ?,?");
		
		return jdbcTemplate.query(sql.toString(),new Object[]{userId,userId,start,pagesize}, new CourseMapper());
	}
	
	
	public void commitSelectedCourse(String courseId,String teachId) {
		String sql = "INSERT INTO course_teach_mapping (course_id,teach_id) values (?,?)";
		jdbcTemplate.update(sql , new Object[]{courseId,teachId});
	}
	
	public void cancelSelectedCourse(String courseId,String teachId){
		jdbcTemplate.update("delete from course_teach_mapping where course_id = ? and teach_id = ? " , new Object[]{courseId,teachId});
	}
	
	public CourseSettingBean getCourseSettingBeanForTestByCourseId(String courseId){
		String 	sql = "select * from course_setting  where course_id = ? and active = 1 and type = 1";
		return (CourseSettingBean) jdbcTemplate.queryForObject(sql, new Object[]{courseId} , new CourseSettingRowMapper());
	}
	
	
	public CourseSettingBean getCourseSetingBean(String id){
		String 	sql = "select * from course_setting  where id = ?";
		return (CourseSettingBean) jdbcTemplate.queryForObject(sql, new Object[]{id} , new CourseSettingRowMapper());
	}
	
	
	public String getSelectCourseId(String courseSetId){
		String sql = "SELECT sc.id FROM course_setting cs "+ 
					" LEFT JOIN selected_course sc ON sc.course_id = cs.course_id  "+
					" WHERE cs.id = ? ";
		return (String) jdbcTemplate.queryForObject(sql, new Object[]{courseSetId} , String.class);
	}
	
	public boolean saveCourseSetting( CourseSettingBean bean){
		String sql = "INSERT INTO course_setting "+
					" (course_id,title,active,score,type,createtime)VALUES(?,?,?,?,?,CURRENT_TIMESTAMP);";
		Object[] objs = new Object[]{bean.getCourseId(),bean.getTitle(),bean.getActive(),bean.getScore(),bean.getType()};
		return jdbcTemplate.update(sql,objs) > 0; 
	}
	
	public boolean updateCourseSetting( CourseSettingBean bean){
		String sql = "update course_setting "+
					" set course_id = ? ,title=?,active=?,score=?,type=? ,updatetime = CURRENT_TIMESTAMP where id = ?;";
		Object[] objs = new Object[]{bean.getCourseId(),bean.getTitle(),bean.getActive(),bean.getScore(),bean.getType() ,bean.getId()};
		return jdbcTemplate.update(sql,objs) > 0; 
	}
	
	public void setCourseSettingActive(final String courseId,final String settingId){
		transactionTemplate.execute(new TransactionCallback() {
			public Object doInTransaction(TransactionStatus arg0) {
				Object point = arg0.createSavepoint();
				try {
					String sql = "update course_setting set active=false where course_id = ?";
					jdbcTemplate.update(sql , new Object[]{ courseId});		
					sql = "update course_setting set active=true where id = ?";
					jdbcTemplate.update(sql , new Object[]{ settingId});		
				} catch (Exception e) {
					arg0.rollbackToSavepoint(point);
				}
				return null;
			}
		});
	}
	
	public void deleteCourseSetting(String id){
		
	}
	
	
	
	
	public class CourseRowMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			CourseBean bean = new CourseBean();
			bean.setId(rs.getString("id"));
			bean.setSpecialty_id(rs.getString("specialty_id"));
//			bean.setSpecialty_name(BaseAction.getInstance().getSpecialtyService().findSpecialtyById(Integer.parseInt(bean.getSpecialty_id())).getSpecialtyname());
			bean.setSpecialty_name("");
			try{
			bean.setTeacherDes(rs.getString("userdescript"));
			}catch (Exception e) {
			}
			try {
				bean.setSpecialty_name(rs.getString("specialtyname"));
			} catch (Exception e) {
			}
			
			bean.setCourse_name(rs.getString("courses_name"));
			bean.setDescripte(rs.getString("descript"));
			bean.setKeys(rs.getString("course_type"));
			bean.setImage(rs.getString("image"));
			bean.setStatus(""+rs.getString("status"));
			try{
				bean.setStudyUsers(rs.getInt("studys"));
			}catch(Exception e){}
			try{
				if(bean.getStatus().equals("1")){
					bean.setLinkDelete(true);
					bean.setLinkEdit(true);
					bean.setStatus_name(BaseAction.getInstance().getMessages("course_page_status_1"));
				}else if(bean.getStatus().equals("2")){
					bean.setStatus_name(BaseAction.getInstance().getMessages("course_page_status_2"));
				}else if(bean.getStatus().equals("3")){
					bean.setLinkDelete(true);
					bean.setLinkEdit(true);
					bean.setEditStatus(true);
					bean.setShowStatus(false);
					bean.setStatus_name(BaseAction.getInstance().getMessages("course_page_status_3"));
				}else if(bean.getStatus().equals("4")){
					bean.setStatus_name(BaseAction.getInstance().getMessages("course_page_status_4"));
				}else if(bean.getStatus().equals("5")){
					bean.setViewMsg(true);
					bean.setStatus_name(BaseAction.getInstance().getMessages("course_page_status_5"));
				}else if(bean.getStatus().equals("6")){
					bean.setLinkDelete(true);
					bean.setLinkEdit(true);
	//				bean.setEditStatus(true);
	//				bean.setShowStatus(false);
					bean.setStatus_name(BaseAction.getInstance().getMessages("course_page_status_6"));
				}else if(bean.getStatus().equals("-1")){
					bean.setStatus_name(BaseAction.getInstance().getMessages("course_page_status_-1"));
				}
			}catch(Exception e){
				log.error("format message failed : ", e);
			}
			
			bean.setHas_test(""+rs.getString("has_test"));
			try{
				if(bean.getHas_test().equals("0")){
					bean.setHas_test_name(BaseAction.getInstance().getMessages("btn_no"));
				}else if(bean.getHas_test().equals("1")){
					bean.setHas_test_name(BaseAction.getInstance().getMessages("btn_yes"));
				}
			}catch(Exception e){}
			
			bean.setTotal_score(rs.getString("total_score"));
			bean.setPrices(rs.getString("prices"));
			bean.setStart_level(rs.getString("start_level"));
			bean.setIs_high_quality(""+rs.getString("is_high_quality"));
			try{
				if(bean.getIs_high_quality().equals("0")){
					bean.setIs_high_quality_name(BaseAction.getInstance().getMessages("btn_no"));
				}else if(bean.getIs_high_quality().equals("1")){
					bean.setIs_high_quality_name(BaseAction.getInstance().getMessages("btn_yes"));
				}
			}catch(Exception e){}
			if(rs.getString("create_time") != null && rs.getString("create_time").length() >= 10)
				bean.setCreate_time((""+rs.getString("create_time")).substring(0,10));
			else
				bean.setCreate_time(""+rs.getString("create_time"));
			try {
				if(rs.getString("online_begin_time") != null)
					bean.setOnline_begin_time((""+rs.getString("online_begin_time")).substring(0,10));
			} catch (Exception e) {
				// TODO: handle exception
			}
			try {
				if(rs.getString("online_end_time") != null)
					bean.setOnline_end_time((""+rs.getString("online_end_time")).substring(0,10));
			} catch (Exception e) {
				// TODO: handle exception
			}
			bean.setCredit(rs.getString("credit"));
			bean.setTeacherId(rs.getString("teacher_id"));
//			bean.setTeacherName(BaseAction.getInstance().getTeacherService().findById(Integer.parseInt(rs.getString("teacher_id"))).getUserName());
			bean.setTeacherName("");
			try {
				bean.setTeacherName(rs.getString("username"));
			} catch (Exception e) {
				//e.printStackTrace();
			}
			try {
				String cdid = rs.getString("cdid");
				if(cdid == null || cdid.equals("") || cdid.equalsIgnoreCase("null")){
					bean.setIsHaveCourseContent(BaseAction.getInstance().getMessages("label_no_have"));
				}else{
					bean.setIsHaveCourseContent(BaseAction.getInstance().getMessages("label_have"));
				}
			} catch (Exception e) {
				// TODO: handle exception
			}
			try {
				bean.setTrainingTotalScore(""+Integer.parseInt(rs.getString("trainingTotalScore")));
			} catch (Exception e) {
				bean.setTrainingTotalScore("0");
			}
			try {
				int cdid = Integer.parseInt(rs.getString("cdid"));
				bean.setHaveChapter(true);
			} catch (Exception e) {
				bean.setHaveChapter(false);
			}
			
			
			bean.setTeaching_group_id(rs.getString("teaching_group_id"));
			try {
				bean.setTeaching_group_name(rs.getString("teaching_group_name"));
			} catch (Exception e) {
				// TODO: handle exception
			}
			bean.setFile_path_id(rs.getInt("file_path_id"));
			try{
				bean.setHasTraining(Integer.parseInt(bean.getHas_test()) > 0);
			}catch(Exception e){
				log.error("", e);
			}
			
			
			bean.setHasSurvey(Integer.parseInt(bean.getStatus()) == 1 || Integer.parseInt(bean.getStatus()) == 3) ;
			
			bean.setCourseResourse(rs.getString("originate"));
			bean.setLanguage_type(rs.getString("language_type"));
			bean.setCompleted(rs.getString("completed"));
			
			try{
				ResourseBean rb = BaseAction.getInstance().getResourseService().getResourseByID(bean.getLanguage_type());
				bean.setLanguageTypeName(rb.getName());
				if(Integer.parseInt(bean.getCompleted()) == 1)
					bean.setCompletedName(BaseAction.getInstance().getMessages("course_page_course_is_completed_yes"));
				else
					bean.setCompletedName(BaseAction.getInstance().getMessages("course_page_course_is_completed_no"));
			}catch(Exception e){
				log.error("format message to failed : ", e);
			}
			
			return bean;
		}
	}
	
	
	/**
	 * 学生选课时查询返回对象
	 * @author renyongcheng
	 *
	 */
	class CourseMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			CourseBean bean = new CourseBean();
			bean.setId(rs.getString("id"));
			bean.setCourse_name(rs.getString("courses_name"));
			bean.setStart_level(rs.getString("start_level"));
			bean.setImage(rs.getString("image"));
			bean.setTeacherId(rs.getString("teacher_id"));
			bean.setTeacherName(rs.getString("username"));
			bean.setFile_path_id(rs.getInt("file_path_id"));
			bean.setCourseResourse(rs.getString("originate"));
			bean.setCreate_time((""+rs.getString("create_time")).substring(0,10));
			try{
				if(rs.getString("ctmid") != null)
					bean.setHasSelected(true);
			}catch(Exception e){}
			return bean;
		}
	}
	
	
	public class CoursesApproveRowMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			CoursesApproveBean bean = new CoursesApproveBean();
			bean.setTime(rs.getString("approve_time").substring(0,19));
			bean.setUsername(rs.getString("userName"));
			bean.setOption(rs.getString("approval_opinion"));
			return bean;
		}
	}
	
	
	public class CoursesNoticeRowMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			CourseNoticeBean bean = new CourseNoticeBean();
			bean.setId(rs.getInt("id"));
			bean.setCourseId(rs.getString("course_id"));
			bean.setNotice(rs.getString("notice"));
			bean.setCreateTime(rs.getString("create_time"));
			return bean;
		}
	}
	
	public class StudentCoursesNoticeRowMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			CourseNoticeBean bean = new CourseNoticeBean();
			bean.setId(rs.getInt("id"));
			bean.setCourseId(rs.getString("course_id"));
			bean.setNotice(rs.getString("notice"));
			bean.setCreateTime(rs.getString("create_time"));
			bean.setCourseName(rs.getString("courses_name"));
			bean.setTeachName("username");
			return bean;
		}
	}
	
	public class CourseSettingRowMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			CourseSettingBean bean = new CourseSettingBean();
			bean.setId(rs.getString("id"));
			bean.setCourseId(rs.getString("course_id"));
			bean.setTitle(rs.getString("title"));
			bean.setScore(rs.getString("score"));
			bean.setActive(rs.getString("active"));
			bean.setCreatetime(rs.getString("createtime").substring(0,19));
			try {
				bean.setUpdatetime(rs.getString("updatetime").substring(0,19));
			} catch (Exception e) {
			}
			bean.setType(rs.getString("type"));
			
			
			if(bean.getActive().equals("0"))
				bean.setActiveName(BaseAction.getInstance().getMessages("user_page_label_status_del"));
			else
				bean.setActiveName(BaseAction.getInstance().getMessages("user_page_label_status_ok"));
			int type = Integer.parseInt(rs.getString("type"));
			switch(type){
			case 1:
				bean.setTypeName(BaseAction.getInstance().getMessages("course_page_course_test"));
				break;
			case 2:
				bean.setActiveName("");
				bean.setTypeName(BaseAction.getInstance().getMessages("course_page_course_training"));
				break;
			case 3:
				bean.setTypeName(BaseAction.getInstance().getMessages("course_page_course_survey_title"));
				break;
		}
			return bean;
		}
	}
	
	
	
	class CourseOutlineRowMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			CourseOutlineBean bean = new CourseOutlineBean();
			bean.setId(rs.getInt("id"));
			bean.setCourseId(rs.getString("course_id"));
			bean.setOutline(rs.getString("outline")); 
			bean.setFilePathId(rs.getInt("file_path_id"));
			return bean;
		}
	}
	
	class CourseEvaluationRowMapper implements RowMapper {
		public Object mapRow(ResultSet rs, int i) throws SQLException {
			CourseEvaluationBean bean = new CourseEvaluationBean();
			bean.setId(rs.getInt("id"));
			bean.setCourseId(rs.getString("course_id"));
			bean.setContents(rs.getString("contents")); 
			return bean;
		}
	}
	
	
	
	class CourseMapping {
		private String id;
		private String level;
		private String tid;
		private String parentTId;
		
		CourseMapping(String s){
			String[] a = s.split(":");
			id = a[0];
			level = a[1];
			tid = a[2];
			parentTId = a[3];
		}
		
		public String getId() {
			return id;
		}
		public void setId(String id) {
			this.id = id;
		}
		public String getLevel() {
			return level;
		}
		public void setLevel(String level) {
			this.level = level;
		}
		public String getTid() {
			return tid;
		}
		public void setTid(String tid) {
			this.tid = tid;
		}
		public String getParentTId() {
			return parentTId;
		}
		public void setParentTId(String parentTId) {
			this.parentTId = parentTId;
		}
		
		
	}
	
	
}
